In database systems, a “transaction” refers to an atomic set of operations performed against a database, which may access, create, modify or delete database data or metadata. A “commit” occurs when the transaction has completed its processing and any changes to the database by the transaction are ready to be permanently implemented in the database system.
Transaction log records can be maintained in a database system to allow data recovery in the event of an error, that may include hardware failure, network failure, process failure, database instance failure, data access conflicts, user errors, and statement failures in database access programs.
Various types of transaction log records can be maintained in a database system for data recovery. One type of log record that may be maintained is the “undo” record. Undo records contain information about changes that were introduced into the database system. For example, if a row in a table was modified, the changes will be stored in the undo record associated with the block of the database system that includes the modified table row.
Conventional database systems do not include built-in mechanisms to undo changes made by a committed transaction without taking database systems offline. Moreover, conventional database systems do not include built-in mechanisms to recover data after several committed transactions took place without taking database systems offline.
There are several data recovery mechanisms currently available and utilized for some type of data recovery, however, neither one solves the above stated problem. For example, a “flashback database” feature is an off-line operation that provides backup of the entire database. Maintaining a backup copy of the entire database system requires configuring and utilizing a lot of resources. Moreover, even when recovery is required for only a single row of a singe table, the entire database system is replaced with the backup copy.
Another existing recovery mechanism, attempting on unsuccessfully solving the above-stated problem, is Create Table As Of (CTAF) query that returns a copy of the specified table as of the specified time. However, this operation does not preserve indexes and constraints and simply re-creates the data. Moreover, if only a single row was changed and needs to be recovered, the CTAF operation re-creates the entire table regardless of its size.
Import/Export operations may be used in conventional database systems to achieve table recoveries in case of system errors, however, import/export operations also recover entire tables even in situations wherein recovery of only a single row of data is necessary. In addition, in order for the import/export operation to succeed, the export operation has to take place at the flashback time.
What is needed, therefore, is a solution that overcomes these and other shortcomings of the prior art.